﻿using ClosedXML.Excel;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.InkML;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using ReportExportTest.Data;
using ReportExportTest.Provider;
using System.Data;
using System.Linq;

namespace ReportExportTest.Controllers
{
    [ApiController]
    [Route("[controller]/[action]")]
    public class ExportController : Controller
    {
        private readonly ILogger<ExportController> _logger;
        private readonly ApplicationDbContext _context;

        public ExportController(ILogger<ExportController> logger, ApplicationDbContext context)
        {
            _logger = logger;
            _context = context;
        }

        #region 测试NPOI的报表导出
        [HttpGet]
        public IActionResult NPOITest()
        {
            var reportData = ToolProvider.GetDataTable();
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("财务报表");
            WriteHeadData(sheet, reportData, workbook);
            WriteBodyData(sheet, reportData);
            //自动调整列宽
            for (int i = 0; i < reportData.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            TestMerge(sheet, reportData, workbook);
            // 将工作簿保存到内存流
            using (MemoryStream memoryStream = new MemoryStream())
            {
                workbook.Write(memoryStream);
                var content = memoryStream.ToArray();

                // 设置响应头
                Response.Headers.Add("Content-Disposition", "attachment; filename=FinancialReport.xlsx");
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                // 返回文件内容
                return File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "NPOI.xlsx");
            }
        }

        /// <summary>
        /// 写入表头
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="reportData"></param>
        private void WriteHeadData(ISheet sheet, DataTable? reportData, IWorkbook workbook)
        {
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < reportData.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(reportData.Columns[i].ColumnName);

                // 创建字体对象
                IFont boldFont = workbook.CreateFont();
                // 设置字体加粗
                boldFont.IsBold = true;
                // 创建单元格样式
                ICellStyle boldCellStyle = workbook.CreateCellStyle();
                // 将加粗字体应用到样式中
                boldCellStyle.SetFont(boldFont);
                cell.CellStyle = boldCellStyle;
            }
        }

        /// <summary>
        /// 写入数据行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="reportData"></param>
        private void WriteBodyData(ISheet sheet, DataTable? reportData)
        {
            for (int i = 0; i < reportData.Rows.Count; i++)
            {
                IRow dataRow = sheet.CreateRow(i + 1);
                for (int j = 0; j < reportData.Columns.Count; j++)
                {
                    ICell cell = dataRow.CreateCell(j);
                    object value = reportData.Rows[i][j];
                    cell.SetCellValue(value.ToString());
                    //if (value != DBNull.Value)
                    //{
                    //    switch (reportData.Columns[j].DataType)
                    //    {
                    //        case Type type when type == typeof(int) || type == typeof(long) || type == typeof(decimal) || type == typeof(double):
                    //            cell.SetCellValue(Convert.ToDouble(value));
                    //            break;
                    //        case Type type when type == typeof(DateTime):
                    //            cell.SetCellValue(Convert.ToDateTime(value));
                    //            break;
                    //        default:
                    //            cell.SetCellValue(value.ToString());
                    //            break;
                    //    }
                    //}
                }
            }
        }

        /// <summary>
        /// 测试合并单元格
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="reportData"></param>
        private void TestMerge(ISheet sheet, DataTable? reportData, IWorkbook workbook)
        {
            int firstRow = 1; // 起始行索引
            int lastRow = 3;  // 结束行索引
            int firstCol = 5; // 起始列索引
            int lastCol = 7;  // 结束列索引
            CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
            sheet.AddMergedRegion(cellRangeAddress);
            IRow row = sheet.GetRow(firstRow);
            if (row == null)
            {
                row = sheet.CreateRow(firstRow);
            }
            ICell cell = row.GetCell(firstCol);
            if (cell == null)
            {
                cell = row.CreateCell(firstCol);
            }
            cell.SetCellValue("合并后的单元格内容");

            // 创建字体对象
            IFont boldFont = workbook.CreateFont();
            // 设置字体颜色
            boldFont.Color = NPOI.SS.UserModel.IndexedColors.Red.Index;
            // 创建单元格样式
            ICellStyle boldCellStyle = workbook.CreateCellStyle();
            // 将字体应用到样式中
            boldCellStyle.SetFont(boldFont);
            cell.CellStyle = boldCellStyle;
        }
        #endregion

        #region ClosedXML的报表导出
        [HttpGet]
        public IActionResult ClosedXML()
        {
            var list = _context.FinancialReport.Where(u => u.TransactionDate.Date == DateTime.Now.AddDays(-1).Date).ToList();
            using (var workbook = new XLWorkbook())
            {
                // 添加一个工作表
                var worksheet = workbook.Worksheets.Add(DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd") + "财务信息");
                //向表中写入数据
                worksheet.Cell(1, 1).Value = "#";
                worksheet.Cell(1, 2).Value = "诊所";
                worksheet.Cell(1, 3).Value = "发货数量"; 
                worksheet.Cell(1, 4).Value = "处方张数";
                var index = 2;
                foreach (var item in list.GroupBy(u => u.Department))
                {
                    var mergedRange = worksheet.Range(index, 1, index+item.Count()-1, 1).Merge();
                    worksheet.Cell(index, 1).Value = item.Key;
                    // 设置合并单元格的字体颜色为红色
                    mergedRange.Style.Font.FontColor = XLColor.Red;
                    // 设置合并单元格内容水平和垂直居中
                    mergedRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    mergedRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    for (int i = 0; i < item.Count(); i++)
                    {
                        worksheet.Cell(i + index, 2).Value =item.ElementAt(i).TransactionDate;
                        worksheet.Cell(i + index, 3).Value =item.ElementAt(i).Amount;
                        worksheet.Cell(i + index, 4).Value =item.ElementAt(i).TransactionType;
                        worksheet.Cell(i + index, 5).Value =item.ElementAt(i).Notes;
                    }
                    index += item.Count();
                }
               

                // 设置表头样式为加粗
                worksheet.FirstRowUsed().Style.Font.Bold = true;

                // 自动调整列宽
                worksheet.ColumnsUsed().AdjustToContents();

                // 将工作簿保存到内存流
                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    var content = stream.ToArray();

                    // 设置响应头
                    var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    var fileName = "ClosedXML.xlsx";
                    Response.Headers.Add("Content-Disposition", $"attachment; filename={fileName}");

                    // 返回文件流
                    return File(content, contentType, fileName);
                }
            }




            var dataTable = ToolProvider.GetDataTable();
            using (var workbook = new XLWorkbook())
            {
                // 添加一个工作表
                var worksheet = workbook.Worksheets.Add(dataTable, "员工信息");

                // 设置表头样式为加粗
                worksheet.FirstRowUsed().Style.Font.Bold = true;

                // 自动调整列宽
                worksheet.ColumnsUsed().AdjustToContents();

                //合并单元格
                var mergedRange = worksheet.Range(2, 6, 4, 8).Merge();
                worksheet.Cell(2, 6).Value = "员工基本信息";
                // 设置合并单元格的字体颜色为红色
                mergedRange.Style.Font.FontColor = XLColor.Red;
                // 设置合并单元格内容水平和垂直居中
                mergedRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                mergedRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;

                // 将工作簿保存到内存流
                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    var content = stream.ToArray();

                    // 设置响应头
                    var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    var fileName = "ClosedXML.xlsx";
                    Response.Headers.Add("Content-Disposition", $"attachment; filename={fileName}");

                    // 返回文件流
                    return File(content, contentType, fileName);
                }
            }
        }
        #endregion 

        #region OpenXml的报表导出
        [HttpGet]
        public IActionResult OpenXml()
        {
            var dataTable = ToolProvider.GetDataTable();
            // 生成 Excel 文件到内存流
            using (var stream = new MemoryStream())
            {
                using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
                {
                    // 创建工作簿部分
                    WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();

                    // 创建工作表部分
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // 将工作表添加到工作簿
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
                    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };
                    sheets.Append(sheet);

                    // 获取工作表数据
                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                    // 写入表头
                    Row headerRow = new Row();
                    foreach (DataColumn column in dataTable.Columns)
                    {
                        Cell cell = new Cell
                        {
                            CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName),
                            DataType = CellValues.String
                        };
                        headerRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(headerRow);

                    // 写入数据行
                    foreach (DataRow row in dataTable.Rows)
                    {
                        Row excelRow = new Row();
                        foreach (DataColumn column in dataTable.Columns)
                        {
                            Cell cell = new Cell
                            {
                                CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(row[column].ToString()),
                                DataType = CellValues.String
                            };
                            excelRow.AppendChild(cell);
                        }
                        sheetData.AppendChild(excelRow);
                    }


                    // 合并单元格（合并第一行的前两列）
                    MergeCells mergeCells;
                    if (worksheetPart.Worksheet.Elements<MergeCells>().Count() > 0)
                    {
                        mergeCells = worksheetPart.Worksheet.Elements<MergeCells>().First();
                    }
                    else
                    {
                        mergeCells = new MergeCells();
                        worksheetPart.Worksheet.Append(mergeCells);
                    }
                    MergeCell mergeCell = new MergeCell() { Reference = new StringValue("F1:H3") };
                    mergeCells.Append(mergeCell);

                    // 创建字体样式
                    WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
                    stylesPart.Stylesheet = new Stylesheet();

                    Fonts fonts = new Fonts();
                    var font = new DocumentFormat.OpenXml.Presentation.Font();
                    // 正确设置字体颜色
                    //font.Color = new Color() { Rgb = new HexBinaryValue("FF0000") };
                    //fonts.Append(font);
                    //stylesPart.Stylesheet.Append(fonts);

                    // 正确设置字体颜色
                    //var color = new DocumentFormat.OpenXml.Spreadsheet.Color();
                    //color.Rgb = new HexBinaryValue("FF0000");
                    //font.AppendChild(color);
                    //fonts.Append(font);
                    //stylesPart.Stylesheet.Append(fonts);

                    // 应用字体样式到合并单元格
                    CellFormat cellFormat = new CellFormat() { FontId = 0 };
                    CellFormats cellFormats = new CellFormats();
                    cellFormats.Append(cellFormat);
                    stylesPart.Stylesheet.Append(cellFormats);

                    // 设置合并单元格的值
                    Cell mergedCell = worksheetPart.Worksheet.Descendants<Cell>().FirstOrDefault(c => c.CellReference == "F1");
                    if (mergedCell != null)
                    {
                        mergedCell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue("合并单元格内容");
                        mergedCell.DataType = CellValues.String;
                        mergedCell.StyleIndex = 0;
                    }

                    // 保存工作簿
                    workbookPart.Workbook.Save();
                }
                var content = stream.ToArray();

                // 设置响应头
                var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                var fileName = "OpenXml.xlsx";
                Response.Headers.Add("Content-Disposition", $"attachment; filename={fileName}");

                // 返回文件流
                return File(content, contentType, fileName);
            }
        }
        #endregion

        #region 测试EPPlus(配置LicenseContext.Commercial需要购买许可证。配置LicenseContext.NonCommercial不可商用。不配置会抛出异常。)
        [HttpGet]
        public async Task<IActionResult> EPPlus()
        {
            // 设置许可证上下文为非商业用途
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            var dataTable = ToolProvider.GetDataTable();
            using (ExcelPackage package = new ExcelPackage())
            {
                // 创建一个工作表
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("报表");

                // 写入表头
                for (int col = 0; col < dataTable.Columns.Count; col++)
                {
                    worksheet.Cells[1, col + 1].Value = dataTable.Columns[col].ColumnName;
                    // 设置表头样式，这里设置为加粗
                    worksheet.Cells[1, col + 1].Style.Font.Bold = true;
                }

                // 写入数据行
                for (int row = 0; row < dataTable.Rows.Count; row++)
                {
                    for (int col = 0; col < dataTable.Columns.Count; col++)
                    {
                        worksheet.Cells[row + 2, col + 1].Value = dataTable.Rows[row][col];
                    }
                }

                // 自动调整列宽
                worksheet.Cells.AutoFitColumns();

                //合并单元格
                worksheet.Cells[2, 5, 4, 7].Merge = true;
                worksheet.Cells[2, 5].Value = "合并单元格内容";
                worksheet.Cells[2, 5].Style.Font.Bold = true;
                worksheet.Cells[2, 5].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                worksheet.Cells[2, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                // 保存到内存流
                var stream = new MemoryStream();
                await package.SaveAsAsync(stream);
                stream.Position = 0;

                // 设置响应头
                var contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                var fileName = "EPPlus.xlsx";
                Response.Headers.Add("Content-Disposition", $"attachment; filename={fileName}");

                // 返回文件流
                return File(stream, contentType, fileName);
            }
        }
        #endregion
    }
}
